﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Xml;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Threading;

namespace SqlFormatter
{

    public partial class MainForm : Form
    {
        
        StringBuilder builder = new StringBuilder();
        PoorMansTSqlFormatterLib.Interfaces.ISqlTokenizer _tokenizer;
        PoorMansTSqlFormatterLib.Interfaces.ISqlTokenParser _parser;
        PoorMansTSqlFormatterLib.Interfaces.ISqlTreeFormatter _formatter;

        public int SpacesPerTab;
        public int MaxLineWidth;
        public bool ExpandCommaLists;
        public bool TrailingCommas;
        public bool SpaceAfterExpandedComma;
        public bool ExpandBooleanExpressions;
        public bool ExpandCaseStatements;
        public bool ExpandBetweenConditions;
        public bool UppercaseKeywords;
        public bool BreakJoinOnSections;
        public bool HTMLColoring;
        public bool KeywordStandardization;
        public static string ConnectionString { get; set; }

        public static string cnnStr { get; set; }

        SplaceScreen sc;
        public MainForm()
        {
            InitializeComponent();

            button2.Enabled = false;
            FSchemabutton.Enabled = false;
            lblProcess.Hide();

           
        }

        public MainForm(SplaceScreen calledForm)
        {
            InitializeComponent();

            button2.Enabled = false;
            FSchemabutton.Enabled = false;
            lblProcess.Hide();

            sc = calledForm;
            sc.Close();
        }

        //Create a Bitmpap Object.

     
        
        //SqlDataAdapter da;
        DataSet ds = new DataSet();

        // Build the Connection String and create a SQL Connection object

        //public static String cnnStr = "Data Source = localhost; Integrated Security = SSPI; MultipleActiveResultSets=True";


        SqlConnection cnn;
         

        private void FSchemabutton_Click(object sender, EventArgs e)
        {
            try
            {
               
                FSchemabutton.Enabled = false;
                cnn = GetSQLConnection();

                SqlCommand UseDB = cnn.CreateCommand();
                
                UseDB.CommandType = CommandType.Text;
                UseDB.CommandText = " USE  " + "[" + DBcomboBox.Text + "]";
                UseDB.ExecuteNonQuery();

                string DatabaseName = DBcomboBox.SelectedValue as string;

                foreach (TreeNode tNode in treeView1.Nodes)
                {
                    //pictureBox1.Invoke((Action)(() => pictureBox1.Visible = true));
                  //  this.pictureBox1.Image = Properties.Resources.progress_indicator;
                    //Cursor.Current = Cursors.WaitCursor;
                    
                    chknode(tNode);
                }

                FSchemabutton.Enabled = true;
              //  pictureBox1.Visible = false;
               // Cursor.Current = Cursors.Default;

               
            }

            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

       
      
        private string DoFormatting(string target)
        {

            PoorMansTSqlFormatterLib.Interfaces.ISqlTreeFormatter _formatter;
            PoorMansTSqlFormatterLib.Interfaces.ISqlTokenParser _parser;
            //_formatter = new PoorMansTSqlFormatterLib.Formatters.HtmlPageWrapper(innerFormatter);


            PoorMansTSqlFormatterLib.Interfaces.ISqlTreeFormatter innerFormatter;
           
                innerFormatter = new PoorMansTSqlFormatterLib.Formatters.TSqlStandardFormatter(new PoorMansTSqlFormatterLib.Formatters.TSqlStandardFormatterOptions
                {
                    IndentString = txtBoxIndent.Text,
                    SpacesPerTab = int.Parse(txtBoxIndentWidth.Text),
                    MaxLineWidth = int.Parse(txtBoxMaxWidth.Text),
                    ExpandCommaLists = chk_ExpandCommaLists.Checked,
                    TrailingCommas = chk_TrailingCommas.Checked,
                    SpaceAfterExpandedComma = chk_SpaceAfterComma.Checked,
                    ExpandBooleanExpressions = chk_ExpandBooleanExpressions.Checked,
                    ExpandCaseStatements = chk_ExpandCaseStatements.Checked,
                    ExpandBetweenConditions = chk_ExpandBetweenConditions.Checked,
                    BreakJoinOnSections = chk_BreakJoinOnSections.Checked,
                    UppercaseKeywords = chk_UppercaseKeywords.Checked
                   // HTMLColoring = chk_Coloring.Checked,
                   // KeywordStandardization = chk_EnableKeywordStandardization.Checked
                });
            //}
            //else if (radio_Formatting_Identity.Checked)
            //    innerFormatter = new PoorMansTSqlFormatterLib.Formatters.TSqlIdentityFormatter(chk_IdentityColoring.Checked);
            //else
            //    innerFormatter = new PoorMansTSqlFormatterLib.Formatters.TSqlObfuscatingFormatter(
            //        chk_RandomizeKeywordCase.Checked,
            //        chk_RandomizeColor.Checked,
            //        chk_RandomizeLineLength.Checked,
            //        chk_PreserveComments.Checked,
            //        chk_KeywordSubstitution.Checked
                    //);

            //innerFormatter.ErrorOutputPrefix = _generalResourceManager.GetString("ParseErrorWarningPrefix") + Environment.NewLine;
            _formatter = new PoorMansTSqlFormatterLib.Formatters.HtmlPageWrapper(innerFormatter);

               _tokenizer = new PoorMansTSqlFormatterLib.Tokenizers.TSqlStandardTokenizer();
            _parser = new PoorMansTSqlFormatterLib.Parsers.TSqlStandardParser();

            var tokenizedSql = _tokenizer.TokenizeSQL(target);
            var parsedSql = _parser.ParseSQL(tokenizedSql);
            return _formatter.FormatSQLTree(parsedSql);

          //  return  webBrowser_OutputSql.SetHTML(_formatter.FormatSQLTree(parsedSql));
       //     SetHTML(_formatter.FormatSQLTree(parsedSql));
            //var tokenizedSql = _tokenizer.TokenizeSQL(target);

            //if (!splitContainer4.Panel2Collapsed && !splitContainer5.Panel1Collapsed)
            //    txt_TokenizedSql.Text = tokenizedSql.PrettyPrint();

            //var parsedSql = _parser.ParseSQL(tokenizedSql);

            //if (!splitContainer4.Panel2Collapsed && !splitContainer5.Panel2Collapsed)
            //    txt_ParsedXml.Text = parsedSql.OuterXml;

            //webBrowser_OutputSql.SetHTML(_formatter.FormatSQLTree(parsedSql));
        }

        public void chknode(TreeNode tn)
        {
           string szFilePath;
           int Counter = 1;

           // Display the ProgressBar control.

           progressBar1.Visible = true;

           // Set Minimum to 1 to represent the first file being copied.
           progressBar1.Minimum = 1;

           // Set the initial value of the ProgressBar.
           progressBar1.Value = 1;

           // Set the Step property to a value of 1 to represent each file being copied.
           progressBar1.Step = 1;
            
           foreach (TreeNode tnn in tn.Nodes)
           {
              
               
               
               if (tnn.Checked)
               {
                   int count = TreeviewCountCheckedNodes(tn.Nodes);
                   lblProcess.Refresh();
                   // Set Maximum to the total number of files to copy.
                   progressBar1.Maximum = count;

                   
                   string TreeViewValue = tnn.Text as string;
                   lblProcess.Show();
                   lblProcess.Text = Counter.ToString() + "/" + count.ToString() + "  objects formatted sucessfully...";
                   formateSelectedSP(TreeViewValue);
                   progressBar1.PerformStep();
                   lblProcess.Refresh();

                   if (TreeViewValue != null)
                   {
                       Counter = Counter + 1;
                   }
                 
               }
           }
        //   pictureBox1.Visible = false;
           progressBar1.Hide();
           pictureBox1.Visible = true;
           lblProcess.Text = "Done; " + (Counter - 1).ToString() + " database objects formatted sucessfully!";
           //lblProcess.Text = "Done!";
        //   MessageBox.Show("Formatted "+(Counter-1).ToString()+ " "+"Stored Procedures "+ "Successfully","Success");
        }

        private void formateSelectedSP(string spName)
        {

            try
            {
                builder.Clear();
                cnn = GetSQLConnection();  

                SqlCommand cmd1 = cnn.CreateCommand();
                cmd1.CommandType = CommandType.Text;
                cmd1.CommandText = "sp_helptext @procname";
                cmd1.Parameters.AddWithValue("procname", spName);

                    
                using (SqlDataReader rdr = cmd1.ExecuteReader())
                {
                    

                    while (rdr.Read())
                    {
                        
                        string SP = (string)rdr["Text"];

                        if (rdr[0].ToString().ToUpper().Contains("CREATE PROC"))
                        {

                            string FirstRow = SP.ToUpper();
                            SP = FirstRow.Replace("CREATE PROC", "Alter Proc");
                             
                        }

                        if (rdr[0].ToString().ToUpper().Contains("CREATE PROCEDURE"))
                        {
                            string FirstRowCase2 = SP.ToUpper();
                            SP = FirstRowCase2.Replace("CREATE PROCEDURE", "Alter Procedure");

                        }

                        
                         
                        builder.Append(SP);
                        //builder.AppendLine();
                        // builder.AppendLine("");
                        //  builder.AppendLine((string)null);

                        // Console.WriteLine(builder.ToString());
                    }

                    

                    //richTextBox2.Text = DoFormatting(builder.ToString());

                    // richTextBox2.Text = Regex.Replace(DoFormatting(builder.ToString()), "<[^>]*>", string.Empty);
                    //richTextBox2.Text = Regex.Replace(builder.ToString(), "<[^>]*>", string.Empty);

                    string FormattedScript = DoFormatting(builder.ToString());

                    //HtmlElement t= new HtmlElement();
                    //  t.InnerHtml = FormattedScript;
                    //  richTextBox2.Text = t.InnerText ;

                    XmlDocument x = new XmlDocument();
                    x.InnerXml = FormattedScript;

                   
                    FormattedScript = x.GetElementsByTagName("pre").Item(0).InnerText;

                   
                    //// MessageBox.Show(FormattedScript);
                    SqlCommand myCommand = new SqlCommand(FormattedScript, cnn);
                    myCommand.ExecuteNonQuery();
                   
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {

            }

           
        }

        private SqlConnection GetSQLConnection()
        {
            if (cnn == null)
            {
                cnn = new SqlConnection(cnnStr.ToString());           
                cnn.Open();
            }
            else if (cnn.State.ToString().Equals("Closed"))
            {
                cnn.Open();
            }

            return cnn;
        }

        public static void PopulateTree(TreeView tree, ICollection<TreeItem> items)
        {
            tree.Nodes.Clear();
            List<TreeNode> roots = new List<TreeNode>();
            roots.Add(tree.Nodes.Add("Stored Procedures"));
            foreach (TreeItem item in items)
            {
                if (item.Level == roots.Count) roots.Add(roots[roots.Count - 1].LastNode);
                roots[item.Level].Nodes.Add(item.procname);
            }
        }

        private void node_AfterCheck(object sender, TreeViewEventArgs e)
        {

            // The code only executes if the user caused the checked state to change.

            if (e.Action != TreeViewAction.Unknown)
            {

                if (e.Node.Nodes.Count > 0)
                {

                    /* Calls the CheckAllChildNodes method, passing in the current 

                    Checked value of the TreeNode whose checked state changed. */
                   // MessageBox.Show("Are u sure you want to select all objects!");
                   // MessageBox.Show("Are you sure you want to select/deselect all Stored Procedures!", "Information", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
                    if (e.Node.Checked == true)
                    {
                        if (MessageBox.Show("Are you sure you want to select all Stored Procedures!", "Information", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                        {
                            this.CheckAllChildNodes(e.Node, e.Node.Checked);
                        }
                    }
                    else
                    {
                        this.CheckAllChildNodes(e.Node, e.Node.Checked = false);
                    }
                }

            }

        }


        private void CheckAllChildNodes(TreeNode treeNode, bool nodeChecked)
        {

            foreach (TreeNode node in treeNode.Nodes)
            {

                node.Checked = nodeChecked;

                if (node.Nodes.Count > 0)
                {

                    // If the current node has child nodes, call the CheckAllChildsNodes method recursively.

                    this.CheckAllChildNodes(node, nodeChecked);

                }

            }

        }

        private int TreeviewCountCheckedNodes(TreeNodeCollection treeNodeCollection)
        {
            int countchecked = 0;
            if (treeNodeCollection != null)
            {
                foreach (TreeNode node in treeNodeCollection)
                {
                    if (node.Nodes.Count == 0 && node.Checked)
                    {
                        countchecked++;
                    }
                    else if (node.Nodes.Count > 0)
                    {
                        countchecked += TreeviewCountCheckedNodes(node.Nodes);
                    }
                }
            }
            return countchecked;
        }


        private void button2_Click(object sender, EventArgs e)
        {
            pictureBox1.Visible = false;
            lblProcess.Hide();

            if (DBcomboBox.Text == "Please select...")
            {
                MessageBox.Show("Please select a valid Database ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return;
            }

            // Implementing TreeView
            List<TreeItem> items = new List<TreeItem>();
            cnn = GetSQLConnection();
            SqlCommand cmd3 = cnn.CreateCommand();
            cmd3.CommandType = CommandType.Text;
            cmd3.CommandText = " SELECT SPECIFIC_SCHEMA+'.'+SPECIFIC_NAME  as procname,ROW_NUMBER() Over (order by SPECIFIC_SCHEMA,SPECIFIC_NAME) as ID FROM " +"["+ DBcomboBox.Text +"]"+ ".INFORMATION_SCHEMA.ROUTINES " +
                               " WHERE SPECIFIC_CATALOG = '"+ DBcomboBox.Text +"' AND ROUTINE_TYPE = 'Procedure'";
          

            SqlDataReader  sqlReader;
            sqlReader = cmd3.ExecuteReader();

           
           // SplaceScreen s = new SplaceScreen();
           // s.IsTrue = false;
          //  s.Show();
            while (sqlReader.Read())
            {
                Cursor.Current = Cursors.WaitCursor;

                int TreeLevel = 0;
                items.Add(new TreeItem(sqlReader[0].ToString(), TreeLevel));
                PopulateTree(treeView1, items);
                TreeLevel++;
            }

            Cursor.Current = Cursors.Default;
            sqlReader.Close();
           
            //int CountofProc = ((Int32?)cmd3.ExecuteScalar()) ?? 0;
            //cnn.Close();
            //for (int i = 1; i < CountofProc; i++)
            //{
            //    int TreeLevel = 0;
            //    SqlCommand cmd4 = new SqlCommand("dbo.FetchProcNameForSelectedDB ", cnn);
            //    cmd4.CommandType = CommandType.StoredProcedure;
            //    cmd4.Parameters.Add("@ID", SqlDbType.Int).Value = i;
            //    cnn.Open();
            //    object result = cmd4.ExecuteScalar();
            //    cnn.Close();
            //    string P = (string)result;

            //    items.Add(new TreeItem(P, TreeLevel));
            //    PopulateTree(treeView1, items);
            //    TreeLevel++;
            //}

        }

        private void connectToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DBConnection dbCon = new DBConnection(this);

            button2.Enabled = true;
            FSchemabutton.Enabled = true;
            dbCon.ShowDialog();
        }

        private void DBcomboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (DBcomboBox.Text != "Please select...")
            {
                cnnStr = " initial catalog = " + DBcomboBox.Text + ";";
                cnnStr = ConnectionString + cnnStr;
            }
        }

    
       

       
        //private void btnSQLLogin_Click(object sender, EventArgs e)
        //{
        //    String cnnStr = "Data Source =" + txtBoxServerName.Text + "; User ID="+txtBoxUserName.Text+";Password="+txtBoxPwd.Text;
        //    cnn.ConnectionString = cnnStr;
        //    button1_Click(null,null);
        //}

       

      

        
       
      
     
       

       

        //public string FormatSQLTree(System.Xml.XmlDocument sqlTree)
        //{
        //    //string formattedResult = _underlyingFormatter.FormatSQLTree(sqlTree);
        //    //if (_underlyingFormatter.HTMLFormatted)
        //    //    return string.Format(HTML_OUTER_PAGE, formattedResult);
        //    //else
        //    //    return string.Format(HTML_OUTER_PAGE, Utils.HtmlEncode(formattedResult));
        //}

        //public void SetHTML(string htmlContent)
        //{
        //    bool allowedNavigation = true;
            
        //   // CoInternetSetFeatureEnabled(DISABLE_NAVIGATION_SOUNDS, SET_FEATURE_ON_PROCESS, true);
        //   // this.Navigate("about:blank");
        //    if (this.Document != null)
        //    {
        //        this.Document.Write(string.Empty);
        //    }
        //    this.DocumentText = htmlContent;
        //    CoInternetSetFeatureEnabled(DISABLE_NAVIGATION_SOUNDS, SET_FEATURE_ON_PROCESS, false);
        //    AllowNavigation = allowedNavigation;
        //}
    }


  
}

public class TreeItem {
    public string procname;
    public int Level; 

    public TreeItem(string name, int level)
    {
        procname = name; Level = level;
    
    }
}